﻿
using STT.Admin;
using STT.Business;
using STT.Layer.Connection;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace STT.Profile.UserControlsGV
{
    public partial class UcBangDiemHocVien : System.Web.UI.UserControl
    {
        ToolBox tb = new ToolBox();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                LoadDDLKhoahoc();
                
        }
        private void LoadDDLKhoahoc()
        {
            Methods.KhoaHoc2(ref ddlkhoahoc,Session["Magv"].ToString());
        }
        //private void LoadByLop()
        //{
        //    DataTable dt = tb.GetDataTableById("[TbDiemHV_ByMaLop]", ddllophoc.SelectedValue.ToString());
        //    gvDiemHV.DataSource = dt;
        //    gvDiemHV.DataBind();
        //    if (dt.Rows.Count > 0)
        //    { btnexport.Visible = true; }
        //    else btnexport.Visible = false;
        //}
        void ThongBao(string Loi)
        {
            ScriptManager.RegisterClientScriptBlock(this, GetType(), "Thông báo!", "<script>alert('" + Loi + "')</script>", false);
        }
        protected void ddlkhoahoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (ddlkhoahoc.SelectedItem.ToString() == "---------Chọn---------")
                {
                    ThongBao("Bạn hãy chọn khóa học để xem..!");

                }
                else
                {
                  
                    Methods.LopHocByKhoaHocID(ref ddllophoc, ddlkhoahoc.SelectedValue.ToString(), Session["Magv"].ToString());
                }
            }
            catch (Exception ex)
            {
                Response.Write("");
            }
        }

        protected void ddllophoc_SelectedIndexChanged(object sender, EventArgs e)
        {

            try
            {

            if (ddllophoc.SelectedItem.ToString() == "---------Chọn---------")
            {
                ThongBao("Bạn hãy chọn lớp học để xem..!");

            }
            else
            {
                pnbangdiem.Visible = false;
                //Methods.MonHocByMaLopMaGV(ref ddlmonhoc, ddllophoc.SelectedValue.ToString(), Session["Magv"].ToString());
            }
            }
            catch (Exception ex)
            {
                Response.Write("");
            }
        }
        private void LoadByMH()
        {
            DataTable dt = BTbGiangVien.GetDiemByMonHoc(ddlmonhoc.SelectedValue.ToString(), ddllophoc.SelectedValue.ToString());
            gvDiemHV.DataSource = dt;
            gvDiemHV.DataBind();
            if (dt.Rows.Count > 0)
            {
                pnbangdiem.Visible = true;
                btnexport.Visible = true;
                lblbangdiem.Visible = true;
                lblbangdiem.Text = "Bảng điểm môn" + " "+dt.Rows[0]["TenMonHoc"].ToString() +" "+ "của" +" "+ dt.Rows[0]["TenLop"].ToString();
            }
            else { 
                btnexport.Visible = false;
            lblbangdiem.Visible = false; 
            }
        }
        protected void ddlmonhoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {

                if (ddllophoc.SelectedItem.ToString() == "---------Chọn---------")
                {
                    ThongBao("Bạn hãy chọn môn học để xem..!");

                }
                else
                {
                    LoadByMH();
                }
            }
            catch (Exception ex)
            {
                Response.Write("");
            }
        }
        protected void gvDiemHV_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (ddllophoc.SelectedItem.ToString() != "---------Chọn---------" && ddlmonhoc.SelectedItem.ToString() != "---------Chọn---------")
            {
                gvDiemHV.PageIndex = e.NewPageIndex;
                LoadByMH();
            }
        }
        public void Export1(DataTable dt, string sheetName, string title)
        {
            //T?o các ð?i tý?ng Excel
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks oBooks;
            Microsoft.Office.Interop.Excel.Sheets oSheets;
            Microsoft.Office.Interop.Excel.Workbook oBook;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            //T?o m?i m?t Excel WorkBook 
            oExcel.Visible = true;
            oExcel.DisplayAlerts = false;
            oExcel.Application.SheetsInNewWorkbook = 1;
            oBooks = oExcel.Workbooks;

            oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            oSheets = oBook.Worksheets;
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
            oSheet.Name = sheetName;

            // T?o ph?n ð?u n?u mu?n
            Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("B1", "E1");
            head.MergeCells = true;
            head.Value2 = title;
            head.Font.Bold = true;
            head.Font.Name = "Tahoma";
            head.Font.Size = "18";
            head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            // T?o tiêu ð? c?t 
            Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
            cl1.Value2 = "Mã học viên";
            cl1.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
            cl2.Value2 = "Họ và tên";
            cl2.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
            cl3.Value2 = "Tên Môn học";
            cl3.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
            cl4.Value2 = "Điểm tổng kết";
            cl4.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
            cl5.Value2 = "Lớp";
            cl5.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
            cl6.Value2 = "Năm Học";
            cl6.ColumnWidth = 13.5;

            Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "F3");
            rowHead.Font.Bold = true;
            // K? vi?n
            rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            // Thi?t l?p màu n?n
            rowHead.Interior.ColorIndex = 15;
            rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


            // T?o m?ng ð?i tý?ng ð? lýu d? toàn b? d? li?u trong DataTable,
            // v? d? li?u ðý?c ðý?c gán vào các Cell trong Excel ph?i thông qua object thu?n.
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

            //Chuy?n d? li?u t? DataTable vào m?ng ð?i tý?ng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    arr[r, c] = dr[c];
                }
            }

            //Thi?t l?p vùng ði?n d? li?u
            int rowStart = 4;
            int columnStart = 1;

            int rowEnd = rowStart + dt.Rows.Count - 1;
            int columnEnd = dt.Columns.Count;

            // Ô b?t ð?u ði?n d? li?u
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
            // Ô k?t thúc ði?n d? li?u
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
            // L?y v? vùng ði?n d? li?u
            Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

            //Ði?n d? li?u vào vùng ð? thi?t l?p
            range.Value2 = arr;

            // K? vi?n
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            // Cãn gi?a c?t STT
            Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
            Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
            oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

        }
        protected void btnexport_Click(object sender, EventArgs e)
        {
            DataTable dt = BTbGiangVien.GetDiemByMonHoc(ddlmonhoc.SelectedValue.ToString(),ddllophoc.SelectedValue.ToString());

            Export1(dt, "Danh sach", "DANH SÁCH ĐIỂM HỌC VIÊN");
        }
    }
}